from openpyxl import load_workbook
import uuid
import random

# 加载工作簿（Excel文件）
workbook = load_workbook(filename='/Users/wilson/Desktop/校友测试数据.xlsx')

# 获取活动的工作表或者通过名称获取工作表
sheet = workbook.active  # 或者 sheet = workbook['Sheet1']

# 读取单元格的值
cell_value = sheet['A1'].value
region_list = ["120000", "330100", "140100", "110000", "420100", "210100", "130100"]

# 遍历行
for row in sheet.iter_rows(values_only=True):
    seq = ""
    tutorId = str(uuid.uuid4()).replace('-', '')
    tutorName = ""
    mobile = ""
    idNo = ""

    tutorCollegeId = str(uuid.uuid4()).replace('-', '')
    collegeId = "378258517935616000accca879ed6649"
    intakeDate = ""
    schoolName = ""
    majorName = ""
    className = ""
    degreeType = random.randint(1, 4)
    auditStatus = random.randint(1, 2)
    auditUser = ""
    auditTime = "2025-08-06 12:11:03"

    jobId = str(uuid.uuid4()).replace('-', '')
    jobName = ""
    companyName = ""
    regionCode = random.choice(region_list)
    industryId = random.randint(1, 15)
    for i in range(len(row)):
        cell = row[i]
        if i == 0 : seq =  cell
        if i == 1: tutorName = cell
        if i == 2: mobile = cell
        if i == 3: idNo = cell
        if i == 4: intakeDate = cell
        if i == 5: schoolName = cell
        if i == 6: majorName = cell
        if i == 7: className = cell
        if i == 8: jobName = cell
        if i == 9: companyName = cell
    if "序号" == seq:
        continue
    if int(seq) <= 500:
        continue
    print(f"INSERT INTO `jiuyexing`.`org_tutor` (`tutor_id`, `tutor_name`, `account`, `id_no`, `login_time`, `login_ip`, `add_user`, `add_time`, `update_user`, `update_time`, `is_delete`, `is_disable`) VALUES ('{tutorId}', '{tutorName}', '{mobile}', '{idNo}', NULL, NULL, NULL, '2025-08-06 11:54:00', NULL, NULL, 0, 0);")
    if auditStatus == 2:
        print(f"INSERT INTO `jiuyexing`.`org_tutor_college` (`tutor_college_id`, `tutor_id`, `college_id`, `mobile`, `intake_date`, `school_name`, `major_name`, `class_name`, `degree`, `tags`, `avatar_url`, `tutor_type`, `alumnus_type`, `alumnus_num`, `apply_time`, `auth_status`, `audit_remark`, `audit_user`, `audit_time`, `audit_num`, `source_type`, `info_update_status`, `info_update_user`, `info_update_time`, `add_user`, `add_time`, `update_user`, `update_time`, `is_delete`, `is_disable`, `is_written_off`) VALUES ('{tutorCollegeId}', '{tutorId}', '{collegeId}', '{mobile}', '{intakeDate}', '{schoolName}', '{majorName}', '{className}', {degreeType}, NULL, 'https://zego-davinci-public.oss-cn-shanghai.aliyuncs.com/prod/tutor/avator/1753353950028.jpg', 1, 0, '25080{seq}', '2025-08-06 12:11:03', {auditStatus}, NULL, '{auditUser}', '{auditTime}', 1, 0, 0, NULL, NULL, '60327896492291276890c8f29ff62c49', '2025-08-06 12:11:03', NULL, NULL, 0, 0, 0);")
    else :
        print(f"INSERT INTO `jiuyexing`.`org_tutor_college` (`tutor_college_id`, `tutor_id`, `college_id`, `mobile`, `intake_date`, `school_name`, `major_name`, `class_name`, `degree`, `tags`, `avatar_url`, `tutor_type`, `alumnus_type`, `alumnus_num`, `apply_time`, `auth_status`, `audit_remark`, `audit_user`, `audit_time`, `audit_num`, `source_type`, `info_update_status`, `info_update_user`, `info_update_time`, `add_user`, `add_time`, `update_user`, `update_time`, `is_delete`, `is_disable`, `is_written_off`) VALUES ('{tutorCollegeId}', '{tutorId}', '{collegeId}', '{mobile}', '{intakeDate}', '{schoolName}', '{majorName}', '{className}', {degreeType}, NULL, 'https://zego-davinci-public.oss-cn-shanghai.aliyuncs.com/prod/tutor/avator/1753353950028.jpg', 1, 0, '25080{seq}', '2025-08-06 12:11:03', {auditStatus}, NULL, NULL, NULL, 0, 0, 0, NULL, NULL, '60327896492291276890c8f29ff62c49', '2025-08-06 12:11:03', NULL, NULL, 0, 0, 0);")
    print(f"INSERT INTO `jiuyexing`.`org_tutor_job` (`tutor_job_id`, `tutor_id`, `college_id`, `tutor_college_id`, `job_name`, `enterprise_name`, `job_time`, `job_desc`, `region_code`, `industry_id`, `job_sort`, `add_user`, `add_time`, `update_user`, `update_time`, `is_delete`) VALUES ('{jobId}', '{tutorId}', '{collegeId}', '{tutorCollegeId}', '{jobName}', '{companyName}', NULL, NULL, '{regionCode}', '{industryId}', 0, '60327896492291276890c8f29ff62c49', '2025-08-06 12:37:03', NULL, NULL, 0);")

